import random
from pymysql import Connection
import 单位信息 as DWXX
import time

def get_grzh(count):
    grzh_len = 9
    zero = grzh_len - len(str(count))
    grzh = "2"
    while zero >= 1:
        grzh += '0'
        zero -= 1
    grzh = grzh + str(count)
    return grzh

xing_list = ['王', '李', '张', '刘', '陈', '杨', '黄', '赵', '吴', '周', '徐', '孙', '马', '朱', '胡',
             '林', '高', '郭', '何', '郑', '梁', '谢', '韩', '唐', '冯', '董', '萧', '程', '曹', '袁',
             '邓', '许', '傅', '沈', '曾', '彭', '吕', '苏', '卢', '蒋', '蔡', '贾', '丁', '魏', '叶', '欧阳', '西门', '诸葛']
ming_list = "长江，亚洲第一长河，蜿蜒东流六千三百多公里，贯穿中国腹地，滋养着辽阔的土地。她以磅礴的气势，孕育了灿烂的中华文明，被亿万华夏儿女亲切地称为“母亲河”。沿途风景秀丽，三峡奇绝，是探险和旅游的胜地。长江不仅是生命之源，也是经济发展的重要动脉，沿岸港口繁忙，航运发达，为沿岸城市带来无限活力。保护长江，就是守护我们共同的家园。";

def get_ming(count):
    ming = ''
    while count > 0:
        ming_index = random.randint(1,len(ming_list) - 1)
        ming_tmp = ming_list[ming_index]
        if ming_tmp != '，' and ming_tmp != '。' and ming_tmp != '“' and ming_tmp != '”':
            ming += ming_tmp
            count -= 1
    return ming

def get_xingming(ming_count):
    xing_index = random.randint(1, len(xing_list) - 1)
    xing = xing_list[xing_index]
    ming = get_ming(ming_count)
    return xing + ming

def get_zjlx():
    return '01'

zjhm_prefix_list = ['110000', '120000', '310000', '440000', '500000', '320000', '370000', '410000', '110000', '429000', '510000', '432522']
zjhm_year_list = ['1990', '1991', '1992', '1993', '1994', '1995', '1996', '1997', '1998', '1999', '2000', '2001', '2002', '2003', '2004', '2005', '2006', '2007', '2008', '2009']
zjhm_month_list = ['01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12']
zjhm_day_list = ['01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12']
def get_zjhm():
    zjhm_prefix_index = random.randint(1, len(zjhm_prefix_list) - 1)
    zjhm_prefix = zjhm_prefix_list[zjhm_prefix_index]
    zjhm_year_index = random.randint(1, len(zjhm_year_list) - 1)
    zjhm_year = zjhm_year_list[zjhm_year_index]
    zjhm_month_index = random.randint(1, len(zjhm_month_list) - 1)
    zjhm_month = zjhm_month_list[zjhm_month_index]
    zjhm_day_index = random.randint(1, len(zjhm_day_list) - 1)
    zjhm_day = zjhm_day_list[zjhm_day_index]
    zjhm_suffix = ''
    count = 1
    while count <= 4:
        zjhm_suffix += str(random.randint(0, 9))
        count += 1
    return zjhm_prefix + zjhm_year + zjhm_month + zjhm_day + zjhm_suffix

def get_xingbie(zjhm):
    # 证件号码的第17位，为偶数，代表女性，为奇数，代表男性
    if int(zjhm[16]) % 2 == 0:
        return 2
    return 1

def get_csny(zjhm):
    return zjhm[6:12]

sjhm_prefix_list = ['131', '132', '133', '134', '135', '136', '137', '138', '139',
                    '147', '150', '151', '157', '158', '159',
                    '166', '176', '177', '178',
                    '180', '181', '182', '183', '184', '185', '186', '187', '188', '189',
                    '199']
def get_sjhm():
    sjhm_prefix_index = random.randint(1, len(sjhm_prefix_list) - 1)
    sjhm_prefix = sjhm_prefix_list[sjhm_prefix_index]
    sjhm_suffix = ''
    count = 0
    while count < 8:
        sjhm_suffix += str(random.randint(0, 9))
        count += 1

    return sjhm_prefix + sjhm_suffix


def get_dwxx_dwzh():
    conn = Connection(
        host='localhost',
        port=3306,
        user='root',
        password='admin'
    )
    conn.select_db('excel')
    cursor = conn.cursor()
    cursor.execute(f'select dwzh from DWXX')
    results: tuple = cursor.fetchall()
    result_list = []
    for result in results:
        result_list.append(result[0])
    return result_list

def get_grxx_count():
    conn = Connection(
        host='localhost',
        port=3306,
        user='root',
        password='admin'
    )
    conn.select_db('excel')
    cursor = conn.cursor()
    cursor.execute("select count(1) as count from grxx")
    results: tuple = cursor.fetchall()
    return results[0][0]

def get_xingming_list(count):
    xingming_list = []
    while count > 0:
        xingming_list.append(get_xingming(2))
        count -= 1
    return xingming_list


def get_grxx_info(batch_size):
    init_count = get_grxx_count();
    count = init_count
    dwzh_list = get_dwxx_dwzh()
    dwzh_len = len(dwzh_list)
    grxx_info = 'insert into GRXX (GRZH, DWZH, XINGMING, ZJLX, ZJHM, XINGBIE, CSNY, SJHM, CREATEDUSER) VALUES '
    # grxx_info = 'insert into GRXX (GRZH, DWZH, XINGMING, CREATEDUSER) VALUES '
    start_time = time.time()
    while count < batch_size + init_count:
        dwzh = dwzh_list[count % dwzh_len]
        zjhm = get_zjhm()
        grxx_info += f"('{get_grzh(count)}', '{dwzh}', '{get_xingming(2)}', '{get_zjlx()}', '{zjhm}', '{get_xingbie(zjhm)}', '{get_csny(zjhm)}', '{get_sjhm()}', 'BATCH'),"
        # grxx_info += f"('{get_grzh(count)}', '{dwzh}', '{get_xingming(2)}', 'BATCH'),"
        count = count + 1
        # print(count)
    end_time = time.time()
    print(f'耗时：{end_time - start_time}')
    grxx_info = grxx_info[0:len(grxx_info)-1:1]
    return grxx_info

def insert_grxx(grxx_insert_sql):
    conn = Connection(
        host='localhost',
        port=3306,
        user='root',
        password='admin',
        autocommit=True
    )
    conn.select_db('excel')
    cursor = conn.cursor()
    cursor.execute(grxx_insert_sql)
    cursor.fetchall()
    print("insert end")


if '__main__' == __name__:
    # count = 0
    # dwzh_list = get_dwxx_dwzh()
    # for dwzh in dwzh_list:
    #     print(dwzh)
    # get_grxx_info(1000)
    # print(get_grxx_info(1000))
    count = 0
    while count < 10:
        insert_grxx(get_grxx_info(100000))
        count += 1